package com.rcp.host;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.rcp.objects.CustomerBean;
import com.rcp.objects.EmployeeBean;
import com.rcp.objects.TableBean;

/**
 * Servlet implementation class ViewTables
 */
@WebServlet("/ViewTables")
public class ViewTables extends HttpServlet {
	private static final long serialVersionUID = 1L;
	
	String className = "com.mysql.jdbc.Driver";
	String url = "jdbc:mysql://cs3.calstatela.edu:3306/cs437group01";
	String dbusername = "cs437group01";
	String dbpassword = "VHj#dj!v";
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public ViewTables() {
        super();
        // TODO Auto-generated constructor stub
    }

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		
		boolean addNew = request.getParameter("add") != null;
		String removeTable = request.getParameter("removetable");
		String assigncustomer = request.getParameter("assigncustomer");
		
		String query = "SELECT Tables.TableID, Tables.EmployeeID, Tables.CustomerID, Customers.FirstName, Customers.LastName FROM Tables LEFT JOIN Customers ON Tables.CustomerID = Customers.CustomerID GROUP BY Tables.EmployeeID DESC, Tables.TableID";
		
		ArrayList<TableBean> tables = new ArrayList<TableBean>();
		ArrayList<EmployeeBean> servers = new ArrayList<EmployeeBean>();
		
		ResultSet resultSet = null;
		
		try{
			
			// Include the driver class
			Class.forName( className );
			
			// Establish a connection to the database
			Connection connection =
				DriverManager.getConnection(url, dbusername, dbpassword);			
			
			// Get a reference to the statement object that will execute our query
			// on the server
			Statement statement = connection.createStatement();
			
			if(addNew){
				
				statement.executeUpdate("INSERT INTO Tables VALUES ()");
				
			}
			
			if(assigncustomer != null){
				
				request.setAttribute("assigncustomer", assigncustomer);
			
				if(request.getParameter("findcustomer") != null){
					
					String keyword = request.getParameter("lastname");
					
					if(keyword.length() > 0){
						
						ArrayList<CustomerBean> customers = new ArrayList<CustomerBean>();
						
						resultSet = statement.executeQuery("SELECT * FROM Customers WHERE FirstName LIKE '%" + keyword + "%' OR LastName LIKE '%" + keyword + "%' OR Address LIKE '%" + keyword + "%' OR PhoneNumber LIKE '%" + keyword + "%'");
						
						while(resultSet.next()){
							customers.add(new CustomerBean(resultSet.getInt("CustomerID"), resultSet.getString("FirstName"), resultSet.getString("LastName"), resultSet.getString("Address"), resultSet.getString("PhoneNumber")));
						}
						
						request.setAttribute("searched", true);
						request.setAttribute("customerresult", customers);
						
						resultSet = null;
						
					}
					
				}
			
			}
			
			if(removeTable != null){
				
				statement.executeUpdate("DELETE FROM Tables WHERE TableID = " + removeTable);
				
			}
			
			if(request.getParameter("dropdown") != null){
				
				if(request.getParameter("serverselect") != null){
					
					String[] serverAndTable = request.getParameter("serverselect").split("x");
					statement.executeUpdate("UPDATE Tables SET EmployeeID = '"+ serverAndTable[0] +"' WHERE TableID = '" + serverAndTable[1] + "'");
					
				}
				
			}
			
			if(request.getParameter("assigncustomerfinal") != null){
				
				String[] customerandtable = request.getParameter("assigncustomerfinal").split("x");
				statement.executeUpdate("UPDATE Tables SET CustomerID = '"+ customerandtable[0] +"' WHERE TableID = '" + customerandtable[1] + "'");
				
			}
			
			if(request.getParameter("removerserver") != null){
				
				String[] serverAndTable = request.getParameter("removerserver").split("x");
				statement.executeUpdate("UPDATE Tables SET EmployeeID = NULL WHERE TableID = '" + serverAndTable[1] + "'");
				
			}
			
			if(request.getParameter("removecustomer") != null){
				
				String[] customerAndTable = request.getParameter("removecustomer").split("x");
				statement.executeUpdate("UPDATE Tables SET CustomerID = NULL WHERE TableID = '" + customerAndTable[1] + "'");
				
			}
			
			
			if(request.getParameter("checkboxswap") != null){
				
				if(request.getParameterValues("checkboxservers") != null){
					
					String[] swapValues = request.getParameterValues("checkboxservers");
					
					if( swapValues.length == 2 ){
						
						String[] first = swapValues[0].split("x");
						String[] second = swapValues[1].split("x");
						
						statement.executeUpdate("UPDATE Tables Set EmployeeID = '" + first[0] + "' WHERE TableID = '" + second[1] + "'");
						statement.executeUpdate("UPDATE Tables Set EmployeeID = '" + second[0] + "' WHERE TableID = '" + first[1] + "'");
						
					}
					
				}
				
			}
			
			resultSet = statement.executeQuery("SELECT * FROM Employees WHERE status = 0");
			
			while (resultSet.next()){
				
				servers.add(new EmployeeBean(resultSet.getInt("EmployeeID"), resultSet.getString("FirstName"), resultSet.getString("LastName"), resultSet.getInt("status")));
				
			}
			
			resultSet = null;
			
			// Execute the query and Store the returned records (ResultSet)
			resultSet = statement.executeQuery(query);
			
			while (resultSet.next()){
				
				
//				System.out.println("id: " + resultSet.getInt("TableID") + " seats: " + resultSet.getInt("Seats") + " employee: " + resultSet.getInt("EmployeeID"));
				tables.add(new TableBean(resultSet.getInt("TableID"), resultSet.getInt("EmployeeID"), resultSet.getInt("CustomerID"), resultSet.getString("FirstName") + " " + resultSet.getString("LastName")));
				
			}

			// Close our DB Connection
			connection.close();
						
		}
		catch(SQLException e){		
			e.printStackTrace();
		}
		catch(ClassNotFoundException e){
			e.printStackTrace();
		}
		
		request.setAttribute("servers", servers);
		request.setAttribute("tables", tables);
		
		String pathToView = "/WEB-INF/host/ViewTables.jsp";
		
		RequestDispatcher dispatcher =
				request.getRequestDispatcher( pathToView );
			
			dispatcher.forward(request, response);
		
		
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		doGet(request, response);
	}

}
